import datetime

import sshtunnel as sshtunnel
from clickhouse_driver import connect

try:
    service_ip = '111.230.25.217'
    service_port = 22
    with sshtunnel.SSHTunnelForwarder(
            (service_ip, service_port),
            ssh_username="work",  # username
            ssh_pkey="C:/Users/junhai/.ssh/id_rsa",  # 公钥
            ssh_password="",  # password
            remote_bind_address=('localhost', 9000)) as server:
        local_port = server.local_bind_port

        conn = connect(f'clickhouse://default:@localhost:{local_port}/game')
        # conn = connect(host='localhost', port=local_port, database='ertb', user='default', password='')

        cursor = conn.cursor()
        cursor.execute('select * from  stream.sdk_log where app_id = 50436 limit 2')
        data = cursor.fetchall()
        # 6.关闭cursor
        cursor.close()
        # 7.关闭connection
        conn.close()
        data_list = []
        for i in data:
            data_dict = {
                "created_at": i[0].strftime('%Y-%m-%d %H:%M:%S'),
                "updated_at": i[1].strftime('%Y-%m-%d %H:%M:%S'),
                "when": i[2],
                "who": i[3],
                "platform": i[4],
                "what": i[5],
                "action": i[6],
                "module": i[7],
                "custom_field_extend": eval(i[8]),
                "data1": i[9],
                "data2": i[10],
                "data3": i[11],
                "data4": i[12],
                "data5": i[13],
                "data6": i[14],
                "data7": i[15],
                "data8": i[16],
                "data9": i[17],
                "op": i[18],
                "jbk": i[19],
                "client_id": i[20],
                "udid": i[21],
                "tz": i[22],
                "lang": i[23],
                "width": i[24],
                "height": i[25],
                "mac": i[26],
                "net": i[27],
                "device_name": i[28],
                "os": i[29],
                "system_version": i[30],
                "apil": i[31],
                "idfa": i[32],
                "idfv": i[33],
                "caid1": i[34],
                "caid2": i[35],
                "imei": i[36],
                "oaid": i[37],
                "android_id": i[38],
                "ip": i[39],
                "wn": i[40],
                "bssid": i[41],
                "brand": i[42],
                "factory": i[43],
                "ua": i[44],
                "emulator": i[45],
                "cpu_model": i[46],
                "rom_size": i[47],
                "ram_size": i[48],
                "unique_id": i[49],
                "sdk_version": i[50],
                "middleware_version": i[51],
                "app_version": i[52],
                "package_name": i[53],
                "app_id": i[54],
                "ad_id": i[55],
                "data_extend": eval(i[56])
            }
            data_list.append(data_dict)
except Exception as e:
    print(str(e))
